<!DOCTYPE HTML>
<html lang="zh-hans" >
    
    <head>
        
        <meta charset="UTF-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge" />
        <title>7.使用窗口函数分析数据 | Apache Drill</title>
        <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
        <meta name="description" content="">
        <meta name="generator" content="GitBook 2.6.6">
        <meta name="author" content="smartloli">
        
        <meta name="HandheldFriendly" content="true"/>
        <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no">
        <meta name="apple-mobile-web-app-capable" content="yes">
        <meta name="apple-mobile-web-app-status-bar-style" content="black">
        <link rel="apple-touch-icon-precomposed" sizes="152x152" href="../gitbook/images/apple-touch-icon-precomposed-152.png">
        <link rel="shortcut icon" href="../gitbook/images/favicon.ico" type="image/x-icon">
        
    <link rel="stylesheet" href="../gitbook/style.css">
    
        
        <link rel="stylesheet" href="../gitbook/plugins/gitbook-plugin-tbfed-pagefooter/footer.css">
        
    
        
        <link rel="stylesheet" href="../gitbook/plugins/gitbook-plugin-highlight/website.css">
        
    
        
        <link rel="stylesheet" href="../gitbook/plugins/gitbook-plugin-fontsettings/website.css">
        
    
    

        
    
    
    <link rel="next" href="../4.安装 Drill/1.Drill 安装介绍.html" />
    
    
    <link rel="prev" href="../3.教程/6.社会媒体分析.html" />
    

        
    </head>
    <body>
        
        
    <div class="book"
        data-level="3.7"
        data-chapter-title="7.使用窗口函数分析数据"
        data-filepath="3.教程/7.使用窗口函数分析数据.md"
        data-basepath=".."
        data-revision="Fri Aug 26 2016 12:40:11 GMT+0800 (CST)"
        data-innerlanguage="">
    

<div class="book-summary">
    <nav role="navigation">
        <ul class="summary">
            
            
            
                
                <li>
                    <a href="http://drill.smartloli.org" target="blank" class="custom-link">Apache Drill</a>
                </li>
            
            

            
            <li class="divider"></li>
            

            
    
        <li class="chapter " data-level="0" data-path="index.html">
            
                
                    <a href="../index.html">
                
                        <i class="fa fa-check"></i>
                        
                        前言
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="1" >
            
            <span><b>1.</b> 1.开始</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="1.1" data-path="1.开始/1.Introduction 介绍.html">
            
                
                    <a href="../1.开始/1.Introduction 介绍.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>1.1.</b>
                        
                        1.Introduction 介绍
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="1.2" data-path="1.开始/2.Why Drill 为什么使用 Drill.html">
            
                
                    <a href="../1.开始/2.Why Drill 为什么使用 Drill.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>1.2.</b>
                        
                        2.Why Drill 为什么使用 Drill
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="2" >
            
            <span><b>2.</b> 2.架构</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="2.1" data-path="2.架构/1.Architecture Introduction 架构介绍.html">
            
                
                    <a href="../2.架构/1.Architecture Introduction 架构介绍.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>2.1.</b>
                        
                        1.Architecture Introduction 架构介绍
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="2.2" data-path="2.架构/2.Drill Query Execution Drill 查询执行.html">
            
                
                    <a href="../2.架构/2.Drill Query Execution Drill 查询执行.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>2.2.</b>
                        
                        2.Drill Query Execution Drill 查询执行
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="2.3" data-path="2.架构/3.Core Modules 核心模块.html">
            
                
                    <a href="../2.架构/3.Core Modules 核心模块.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>2.3.</b>
                        
                        3.Core Modules 核心模块
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="2.4" data-path="2.架构/4.Performance 性能.html">
            
                
                    <a href="../2.架构/4.Performance 性能.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>2.4.</b>
                        
                        4.Performance 性能
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="3" >
            
            <span><b>3.</b> 3.教程</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="3.1" data-path="3.教程/1.Tutorials Introduction 教程介绍.html">
            
                
                    <a href="../3.教程/1.Tutorials Introduction 教程介绍.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.1.</b>
                        
                        1.Tutorials Introduction 教程介绍
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="3.2" data-path="3.教程/2.Drill in 10 Minutes 快速指导.html">
            
                
                    <a href="../3.教程/2.Drill in 10 Minutes 快速指导.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.2.</b>
                        
                        2.Drill in 10 Minutes 快速指导
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="3.3" data-path="3.教程/3.A-Y-A-D 数据集分析.html">
            
                
                    <a href="../3.教程/3.A-Y-A-D 数据集分析.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.3.</b>
                        
                        3.A-Y-A-D 数据集分析
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="3.4" >
            
            <span><b>3.4.</b> 4.在 MapR 的 Sandbox 中学习 Drill</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="3.4.1" data-path="3.教程/4.在 MapR 的 Sandbox 中学习 Drill/1.关于 MapR 的 Sandbox.html">
            
                
                    <a href="../3.教程/4.在 MapR 的 Sandbox 中学习 Drill/1.关于 MapR 的 Sandbox.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.4.1.</b>
                        
                        1.关于 MapR 的 Sandbox
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="3.4.2" data-path="3.教程/4.在 MapR 的 Sandbox 中学习 Drill/2.在 Sandbox 中安装 Drill.html">
            
                
                    <a href="../3.教程/4.在 MapR 的 Sandbox 中学习 Drill/2.在 Sandbox 中安装 Drill.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.4.2.</b>
                        
                        2.在 Sandbox 中安装 Drill
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="3.4.3" data-path="3.教程/4.在 MapR 的 Sandbox 中学习 Drill/3.在 Sandbox 中使用 Drill.html">
            
                
                    <a href="../3.教程/4.在 MapR 的 Sandbox 中学习 Drill/3.在 Sandbox 中使用 Drill.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.4.3.</b>
                        
                        3.在 Sandbox 中使用 Drill
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="3.4.4" data-path="3.教程/4.在 MapR 的 Sandbox 中学习 Drill/4.课程1:学习数据集.html">
            
                
                    <a href="../3.教程/4.在 MapR 的 Sandbox 中学习 Drill/4.课程1:学习数据集.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.4.4.</b>
                        
                        4.课程1:学习数据集
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="3.4.5" data-path="3.教程/4.在 MapR 的 Sandbox 中学习 Drill/5.课程2:使用 ANSI SQL 查询.html">
            
                
                    <a href="../3.教程/4.在 MapR 的 Sandbox 中学习 Drill/5.课程2:使用 ANSI SQL 查询.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.4.5.</b>
                        
                        5.课程2:使用 ANSI SQL 查询
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="3.4.6" data-path="3.教程/4.在 MapR 的 Sandbox 中学习 Drill/6.课程3:查询复杂的数据类型.html">
            
                
                    <a href="../3.教程/4.在 MapR 的 Sandbox 中学习 Drill/6.课程3:查询复杂的数据类型.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.4.6.</b>
                        
                        6.课程3:查询复杂的数据类型
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="3.4.7" data-path="3.教程/4.在 MapR 的 Sandbox 中学习 Drill/7.总结.html">
            
                
                    <a href="../3.教程/4.在 MapR 的 Sandbox 中学习 Drill/7.总结.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.4.7.</b>
                        
                        7.总结
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="3.5" data-path="3.教程/5.高度动态的数据集分析.html">
            
                
                    <a href="../3.教程/5.高度动态的数据集分析.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.5.</b>
                        
                        5.高度动态的数据集分析
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="3.6" data-path="3.教程/6.社会媒体分析.html">
            
                
                    <a href="../3.教程/6.社会媒体分析.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.6.</b>
                        
                        6.社会媒体分析
                    </a>
            
            
        </li>
    
        <li class="chapter active" data-level="3.7" data-path="3.教程/7.使用窗口函数分析数据.html">
            
                
                    <a href="../3.教程/7.使用窗口函数分析数据.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>3.7.</b>
                        
                        7.使用窗口函数分析数据
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="4" >
            
            <span><b>4.</b> 4.安装 Drill</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="4.1" data-path="4.安装 Drill/1.Drill 安装介绍.html">
            
                
                    <a href="../4.安装 Drill/1.Drill 安装介绍.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>4.1.</b>
                        
                        1.Drill 安装介绍
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="4.2" data-path="4.安装 Drill/2.迁移 Parquet 数据.html">
            
                
                    <a href="../4.安装 Drill/2.迁移 Parquet 数据.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>4.2.</b>
                        
                        2.迁移 Parquet 数据
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="4.3" >
            
            <span><b>4.3.</b> 3.安装 Drill 到嵌入模式</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="4.3.1" data-path="4.安装 Drill/3.安装 Drill 到嵌入模式/1.嵌入模式的先决条件.html">
            
                
                    <a href="../4.安装 Drill/3.安装 Drill 到嵌入模式/1.嵌入模式的先决条件.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>4.3.1.</b>
                        
                        1.嵌入模式的先决条件
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="4.3.2" data-path="4.安装 Drill/3.安装 Drill 到嵌入模式/2.在 Linux 和 Mac OS X 上安装 Drill.html">
            
                
                    <a href="../4.安装 Drill/3.安装 Drill 到嵌入模式/2.在 Linux 和 Mac OS X 上安装 Drill.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>4.3.2.</b>
                        
                        2.在 Linux 和 Mac OS X 上安装 Drill
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="4.3.3" data-path="4.安装 Drill/3.安装 Drill 到嵌入模式/3.在 Linux 和 Mac OS X 上启动 Drill.html">
            
                
                    <a href="../4.安装 Drill/3.安装 Drill 到嵌入模式/3.在 Linux 和 Mac OS X 上启动 Drill.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>4.3.3.</b>
                        
                        3.在 Linux 和 Mac OS X 上启动 Drill
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="4.3.4" data-path="4.安装 Drill/3.安装 Drill 到嵌入模式/4.在 Windows 上安装 Drill.html">
            
                
                    <a href="../4.安装 Drill/3.安装 Drill 到嵌入模式/4.在 Windows 上安装 Drill.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>4.3.4.</b>
                        
                        4.在 Windows 上安装 Drill
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="4.3.5" data-path="4.安装 Drill/3.安装 Drill 到嵌入模式/5.在 Windows 上启动 Drill.html">
            
                
                    <a href="../4.安装 Drill/3.安装 Drill 到嵌入模式/5.在 Windows 上启动 Drill.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>4.3.5.</b>
                        
                        5.在 Windows 上启动 Drill
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="4.4" >
            
            <span><b>4.4.</b> 4.安装 Drill 到分布式模式</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="4.4.1" data-path="4.安装 Drill/4.安装 Drill 到分布式模式/1.分布式模式的先决条件.html">
            
                
                    <a href="../4.安装 Drill/4.安装 Drill 到分布式模式/1.分布式模式的先决条件.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>4.4.1.</b>
                        
                        1.分布式模式的先决条件
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="4.4.2" data-path="4.安装 Drill/4.安装 Drill 到分布式模式/2.在集群上安装 Drill.html">
            
                
                    <a href="../4.安装 Drill/4.安装 Drill 到分布式模式/2.在集群上安装 Drill.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>4.4.2.</b>
                        
                        2.在集群上安装 Drill
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="4.4.3" data-path="4.安装 Drill/4.安装 Drill 到分布式模式/3.在分布式模式下启动 Drill.html">
            
                
                    <a href="../4.安装 Drill/4.安装 Drill 到分布式模式/3.在分布式模式下启动 Drill.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>4.4.3.</b>
                        
                        3.在分布式模式下启动 Drill
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="4.5" data-path="4.安装 Drill/5.启动 Web 控制台.html">
            
                
                    <a href="../4.安装 Drill/5.启动 Web 控制台.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>4.5.</b>
                        
                        5.启动 Web 控制台
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="5" >
            
            <span><b>5.</b> 5.配置 Drill</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="5.1" data-path="5.配置 Drill/1.配置介绍.html">
            
                
                    <a href="../5.配置 Drill/1.配置介绍.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.1.</b>
                        
                        1.配置介绍
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.2" data-path="5.配置 Drill/2.配置内存.html">
            
                
                    <a href="../5.配置 Drill/2.配置内存.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.2.</b>
                        
                        2.配置内存
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.3" >
            
            <span><b>5.3.</b> 3.配置一个多租户集群</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="5.3.1" data-path="5.配置 Drill/3.配置一个多租户集群/1.配置一个多租户介绍.html">
            
                
                    <a href="../5.配置 Drill/3.配置一个多租户集群/1.配置一个多租户介绍.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.3.1.</b>
                        
                        1.配置一个多租户介绍
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.3.2" data-path="5.配置 Drill/3.配置一个多租户集群/2.配置多租户资源.html">
            
                
                    <a href="../5.配置 Drill/3.配置一个多租户集群/2.配置多租户资源.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.3.2.</b>
                        
                        2.配置多租户资源
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.3.3" data-path="5.配置 Drill/3.配置一个多租户集群/3.配置 Drillbit 共享资源.html">
            
                
                    <a href="../5.配置 Drill/3.配置一个多租户集群/3.配置 Drillbit 共享资源.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.3.3.</b>
                        
                        3.配置 Drillbit 共享资源
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="5.4" data-path="5.配置 Drill/4.配置模拟身份.html">
            
                
                    <a href="../5.配置 Drill/4.配置模拟身份.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.4.</b>
                        
                        4.配置模拟身份
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.5" data-path="5.配置 Drill/5.配置用户身份验证.html">
            
                
                    <a href="../5.配置 Drill/5.配置用户身份验证.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.5.</b>
                        
                        5.配置用户身份验证
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.6" data-path="5.配置 Drill/6.配置 Hive 用户身份验证.html">
            
                
                    <a href="../5.配置 Drill/6.配置 Hive 用户身份验证.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.6.</b>
                        
                        6.配置 Hive 用户身份验证
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.7" data-path="5.配置 Drill/7.配置 Web 控制台和 REST API 安全.html">
            
                
                    <a href="../5.配置 Drill/7.配置 Web 控制台和 REST API 安全.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.7.</b>
                        
                        7.配置 Web 控制台和 REST API 安全
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.8" >
            
            <span><b>5.8.</b> 8.可选配置</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="5.8.1" data-path="5.配置 Drill/8.可选配置/1.可选配置介绍.html">
            
                
                    <a href="../5.配置 Drill/8.可选配置/1.可选配置介绍.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.8.1.</b>
                        
                        1.可选配置介绍
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.8.2" data-path="5.配置 Drill/8.可选配置/2.启动可选项.html">
            
                
                    <a href="../5.配置 Drill/8.可选配置/2.启动可选项.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.8.2.</b>
                        
                        2.启动可选项
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.8.3" data-path="5.配置 Drill/8.可选配置/3.计划和执行可选项.html">
            
                
                    <a href="../5.配置 Drill/8.可选配置/3.计划和执行可选项.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.8.3.</b>
                        
                        3.计划和执行可选项
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.8.4" data-path="5.配置 Drill/8.可选配置/4.持久化配置存储.html">
            
                
                    <a href="../5.配置 Drill/8.可选配置/4.持久化配置存储.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.8.4.</b>
                        
                        4.持久化配置存储
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="5.9" data-path="5.配置 Drill/9.Drill 端口使用.html">
            
                
                    <a href="../5.配置 Drill/9.Drill 端口使用.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.9.</b>
                        
                        9.Drill 端口使用
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="5.10" data-path="5.配置 Drill/10.配置 Drill 终端.html">
            
                
                    <a href="../5.配置 Drill/10.配置 Drill 终端.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>5.10.</b>
                        
                        10.配置 Drill 终端
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="6" >
            
            <span><b>6.</b> 6.连接数据源</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="6.1" data-path="6.连接数据源/1.介绍.html">
            
                
                    <a href="../6.连接数据源/1.介绍.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>6.1.</b>
                        
                        1.介绍
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="6.2" data-path="6.连接数据源/2.注册存储插件.html">
            
                
                    <a href="../6.连接数据源/2.注册存储插件.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>6.2.</b>
                        
                        2.注册存储插件
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="6.3" >
            
            <span><b>6.3.</b> 3.配置存储插件</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="6.3.1" data-path="6.连接数据源/3.配置存储插件/1.插件基础设置.html">
            
                
                    <a href="../6.连接数据源/3.配置存储插件/1.插件基础设置.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>6.3.1.</b>
                        
                        1.插件基础设置
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="6.3.2" data-path="6.连接数据源/3.配置存储插件/2.工作区间.html">
            
                
                    <a href="../6.连接数据源/3.配置存储插件/2.工作区间.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>6.3.2.</b>
                        
                        2.工作区间
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="6.3.3" data-path="6.连接数据源/3.配置存储插件/3.Drill 默认输出格式.html">
            
                
                    <a href="../6.连接数据源/3.配置存储插件/3.Drill 默认输出格式.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>6.3.3.</b>
                        
                        3.Drill 默认输出格式
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="6.4" data-path="6.连接数据源/4.文件系统存储插件.html">
            
                
                    <a href="../6.连接数据源/4.文件系统存储插件.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>6.4.</b>
                        
                        4.文件系统存储插件
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="6.5" data-path="6.连接数据源/5.HBase 存储插件.html">
            
                
                    <a href="../6.连接数据源/5.HBase 存储插件.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>6.5.</b>
                        
                        5.HBase 存储插件
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="6.6" data-path="6.连接数据源/6.Hive 存储插件.html">
            
                
                    <a href="../6.连接数据源/6.Hive 存储插件.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>6.6.</b>
                        
                        6.Hive 存储插件
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="6.7" data-path="6.连接数据源/7.RDBMS 存储插件.html">
            
                
                    <a href="../6.连接数据源/7.RDBMS 存储插件.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>6.7.</b>
                        
                        7.RDBMS 存储插件
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="6.8" data-path="6.连接数据源/8.MongoDB 存储插件.html">
            
                
                    <a href="../6.连接数据源/8.MongoDB 存储插件.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>6.8.</b>
                        
                        8.MongoDB 存储插件
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="6.9" data-path="6.连接数据源/9.S3 存储插件.html">
            
                
                    <a href="../6.连接数据源/9.S3 存储插件.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>6.9.</b>
                        
                        9.S3 存储插件
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="7" >
            
            <span><b>7.</b> 7.ODBC 和 JDBC 接口</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="7.1" data-path="7.ODBC 和 JDBC 接口/1.介绍.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/1.介绍.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.1.</b>
                        
                        1.介绍
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.2" data-path="7.ODBC 和 JDBC 接口/2.使用 JDBC 驱动.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/2.使用 JDBC 驱动.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.2.</b>
                        
                        2.使用 JDBC 驱动
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.3" data-path="7.ODBC 和 JDBC 接口/3.在 Windows 上使用 JDBC 和 SQuirreL.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/3.在 Windows 上使用 JDBC 和 SQuirreL.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.3.</b>
                        
                        3.在 Windows 上使用 JDBC 和 SQuirreL
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.4" >
            
            <span><b>7.4.</b> 4.安装 ODBC 驱动</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="7.4.1" data-path="7.ODBC 和 JDBC 接口/4.安装 ODBC 驱动/1.在 Linux 上安装驱动.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/4.安装 ODBC 驱动/1.在 Linux 上安装驱动.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.4.1.</b>
                        
                        1.在 Linux 上安装驱动
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.4.2" data-path="7.ODBC 和 JDBC 接口/4.安装 ODBC 驱动/2.在 Mac OS X 上安装驱动.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/4.安装 ODBC 驱动/2.在 Mac OS X 上安装驱动.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.4.2.</b>
                        
                        2.在 Mac OS X 上安装驱动
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.4.3" data-path="7.ODBC 和 JDBC 接口/4.安装 ODBC 驱动/3.在 Windows 上安装驱动.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/4.安装 ODBC 驱动/3.在 Windows 上安装驱动.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.4.3.</b>
                        
                        3.在 Windows 上安装驱动
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.4.4" data-path="7.ODBC 和 JDBC 接口/4.安装 ODBC 驱动/4.在 Windows 上安装 TDC 文件.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/4.安装 ODBC 驱动/4.在 Windows 上安装 TDC 文件.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.4.4.</b>
                        
                        4.在 Windows 上安装 TDC 文件
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="7.5" >
            
            <span><b>7.5.</b> 5.配置 ODBC</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="7.5.1" data-path="7.ODBC 和 JDBC 接口/5.配置 ODBC/1.ODBC 配置参考.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/5.配置 ODBC/1.ODBC 配置参考.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.5.1.</b>
                        
                        1.ODBC 配置参考
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.5.2" data-path="7.ODBC 和 JDBC 接口/5.配置 ODBC/2.在 Linux 上配置 ODBC.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/5.配置 ODBC/2.在 Linux 上配置 ODBC.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.5.2.</b>
                        
                        2.在 Linux 上配置 ODBC
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.5.3" data-path="7.ODBC 和 JDBC 接口/5.配置 ODBC/3.在 Max OS X 上配置 ODBC.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/5.配置 ODBC/3.在 Max OS X 上配置 ODBC.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.5.3.</b>
                        
                        3.在 Max OS X 上配置 ODBC
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.5.4" data-path="7.ODBC 和 JDBC 接口/5.配置 ODBC/4.在 Windows 上配置 ODBC.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/5.配置 ODBC/4.在 Windows 上配置 ODBC.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.5.4.</b>
                        
                        4.在 Windows 上配置 ODBC
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.5.5" data-path="7.ODBC 和 JDBC 接口/5.配置 ODBC/5.测试 ODBC 连接.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/5.配置 ODBC/5.测试 ODBC 连接.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.5.5.</b>
                        
                        5.测试 ODBC 连接
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="7.6" >
            
            <span><b>7.6.</b> 6.使用 Drill</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="7.6.1" data-path="7.ODBC 和 JDBC 接口/6.使用 Drill/1.介绍.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/6.使用 Drill/1.介绍.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.6.1.</b>
                        
                        1.介绍
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.6.2" data-path="7.ODBC 和 JDBC 接口/6.使用 Drill/2.连接 Drill 到数据源.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/6.使用 Drill/2.连接 Drill 到数据源.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.6.2.</b>
                        
                        2.连接 Drill 到数据源
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.6.3" data-path="7.ODBC 和 JDBC 接口/6.使用 Drill/3.浏览数据和定义视图.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/6.使用 Drill/3.浏览数据和定义视图.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.6.3.</b>
                        
                        3.浏览数据和定义视图
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    
        <li class="chapter " data-level="7.7" >
            
            <span><b>7.7.</b> 7.用 BI 工具连接 Drill</span>
            
            
            <ul class="articles">
                
    
        <li class="chapter " data-level="7.7.1" data-path="7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/1.介绍.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/1.介绍.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.7.1.</b>
                        
                        1.介绍
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.7.2" data-path="7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/2.Tableau 示例.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/2.Tableau 示例.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.7.2.</b>
                        
                        2.使用 JDBC 驱动
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.7.3" data-path="7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/3.使用 MicroStrategy 分析.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/3.使用 MicroStrategy 分析.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.7.3.</b>
                        
                        3.在 Windows 上使用 JDBC 和 SQuirreL
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.7.4" data-path="7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/4.使用 Tibco Spotfire 客户端.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/4.使用 Tibco Spotfire 客户端.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.7.4.</b>
                        
                        4.使用 Tibco Spotfire 客户端
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.7.5" data-path="7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/5.使用 Qlik Sense.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/5.使用 Qlik Sense.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.7.5.</b>
                        
                        5.使用 Qlik Sense
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.7.6" data-path="7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/6.使用 Tableau 9 客户端.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/6.使用 Tableau 9 客户端.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.7.6.</b>
                        
                        6.使用 Tableau 9 客户端
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.7.7" data-path="7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/7.使用 Tableau 9 服务端.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/7.使用 Tableau 9 服务端.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.7.7.</b>
                        
                        7.使用 Tableau 9 服务端
                    </a>
            
            
        </li>
    
        <li class="chapter " data-level="7.7.8" data-path="7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/8.配置 JReport.html">
            
                
                    <a href="../7.ODBC 和 JDBC 接口/7.用 BI 工具连接 Drill/8.配置 JReport.html">
                
                        <i class="fa fa-check"></i>
                        
                            <b>7.7.8.</b>
                        
                        8.配置 JReport
                    </a>
            
            
        </li>
    

            </ul>
            
        </li>
    

            </ul>
            
        </li>
    


            
            <li class="divider"></li>
            <li>
                <a href="https://www.gitbook.com" target="blank" class="gitbook-link">
                    本书使用 GitBook 发布
                </a>
            </li>
            
        </ul>
    </nav>
</div>

    <div class="book-body">
        <div class="body-inner">
            <div class="book-header" role="navigation">
    <!-- Actions Left -->
    

    <!-- Title -->
    <h1>
        <i class="fa fa-circle-o-notch fa-spin"></i>
        <a href="../" >Apache Drill</a>
    </h1>
</div>

            <div class="page-wrapper" tabindex="-1" role="main">
                <div class="page-inner">
                
                
                    <section class="normal" id="section-">
                    
                        <h1 id="&#x4F7F;&#x7528;&#x7A97;&#x53E3;&#x51FD;&#x6570;&#x5206;&#x6790;&#x6570;&#x636E;">&#x4F7F;&#x7528;&#x7A97;&#x53E3;&#x51FD;&#x6570;&#x5206;&#x6790;&#x6570;&#x636E;</h1>
<p>&#x672C;&#x6559;&#x7A0B;&#x7B80;&#x8981;&#x4ECB;&#x7ECD; Drill 1.2 &#x7684;&#x5206;&#x6790;&#xFF0C;&#x5373;&#x5206;&#x6790;&#x7A97;&#x53E3;&#x51FD;&#x6570;&#x7684; ANSI SQL &#x6807;&#x51C6;&#x3002;Drill &#x652F;&#x6301;&#x4EE5;&#x4E0B;&#x7A97;&#x53E3;&#x51FD;&#x6570;&#xFF1A;</p>
<ul>
<li>PARTITION BY &#x548C; OVER &#x8BED;&#x6CD5;</li>
<li>&#x4E0D;&#x540C;&#x79CD;&#x7C7B;&#x7684;&#x805A;&#x5408;&#x51FD;&#x6570;&#xFF0C;&#x5982; Sum&#xFF0C;Max&#xFF0C;Min&#xFF0C;Count&#xFF0C;Avg</li>
<li>&#x89E3;&#x6790;&#x51FD;&#x6570;&#xFF0C;&#x4F8B;&#x5982; First_Value, Last_Value, Lead, Lag, NTile, Row_Number &#x548C; Rank</li>
</ul>
<p>&#x7A97;&#x53E3;&#x51FD;&#x6570;&#x662F;&#x591A;&#x529F;&#x80FD;&#x7684;&#x3002;&#x4F60;&#x53EF;&#x4EE5;&#x51CF;&#x5C11;&#x8FDE;&#x63A5;&#xFF0C;&#x5B50;&#x67E5;&#x8BE2;&#xFF0C;&#x548C;&#x663E;&#x793A;&#x6E38;&#x6807;&#xFF0C;&#x4F60;&#x53EA;&#x9700;&#x8981;&#x5199;&#x800C;&#x5DF2;&#x3002;&#x7A97;&#x53E3;&#x51FD;&#x6570;&#x4EE5;&#x6700;&#x5C0F;&#x7684;&#x7F16;&#x7801;&#x5DE5;&#x4F5C;&#xFF0C;&#x89E3;&#x51B3;&#x4E86;&#x5404;&#x79CD;&#x590D;&#x6742;&#x7684;&#x60C5;&#x51B5;&#x3002;</p>
<p>&#x672C;&#x6559;&#x7A0B;&#x5EFA;&#x7ACB;&#x5728;&#x524D;&#x9762;&#x7684;&#x6559;&#x7A0B;&#x4E4B;&#x4E0A;&#xFF0C;<a href="3.A-Y-A-D%20&#x6570;&#x636E;&#x96C6;&#x5206;&#x6790;.md">A-Y-A-D &#x6570;&#x636E;&#x5206;&#x6790;</a> &#x548C; <a href="5.&#x9AD8;&#x5EA6;&#x52A8;&#x6001;&#x7684;&#x6570;&#x636E;&#x96C6;&#x5206;&#x6790;.html">&#x9AD8;&#x5EA6;&#x52A8;&#x6001;&#x7684;&#x6570;&#x636E;&#x96C6;&#x5206;&#x6790;</a>&#xFF0C;&#x5E76;&#x4E14;&#x4F7F;&#x7528;&#x7684;&#x662F;&#x76F8;&#x540C;&#x7684; Yelp &#x6570;&#x636E;&#x96C6;&#x3002;</p>
<h2 id="&#x5F00;&#x59CB;">&#x5F00;&#x59CB;</h2>
<ol>
<li>&#x5728;&#x5F00;&#x59CB;&#x524D;&#xFF0C;&#x4E0B;&#x8F7D; Yelp&#xFF08;&#x5546;&#x4E1A;&#x8BC4;&#x8BBA;&#xFF09;&#x3002;</li>
<li><a href="3.A-Y-A-D%20&#x6570;&#x636E;&#x96C6;&#x5206;&#x6790;.md">&#x5B89;&#x88C5;&#x5E76;&#x542F;&#x52A8; Drill</a>&#x3002;</li>
<li><p>&#x5728; Drill &#x4E2D;&#x5217;&#x51FA;&#x53EF;&#x7528;&#x7684; Schema&#x3002;</p>
<pre><code class="lang-bash">SHOW schemas;
+---------------------+
|     SCHEMA_NAME     |
+---------------------+
| INFORMATION_SCHEMA  |
| cp.default          |
| dfs.default         |
| dfs.root            |
| dfs.tmp             |
| dfs.yelp            |
| sys                 |
+---------------------+

<span class="hljs-number">7</span> rows selected (<span class="hljs-number">1.755</span> seconds)
</code></pre>
</li>
<li><p>&#x5207;&#x6362;&#x5DE5;&#x4F5C;&#x76EE;&#x5F55;&#x3002;</p>
<pre><code class="lang-bash">USE dfs.yelp;

+-------+---------------------------------------+
|  ok   |                summary                |
+-------+---------------------------------------+
| <span class="hljs-literal">true</span>  | Default schema changed to [dfs.yelp]  |
+-------+---------------------------------------+

<span class="hljs-number">1</span> row selected (<span class="hljs-number">0.129</span> seconds)
</code></pre>
</li>
<li><p>&#x5F00;&#x59CB;&#x63A2;&#x7D22; Yelp &#x4E2D;&#x53EF;&#x7528;&#x7684;&#x6570;&#x636E;&#x96C6;&#x4FE1;&#x606F;&#x3002;</p>
<pre><code class="lang-bash">SELECT * FROM `business.json` LIMIT <span class="hljs-number">1</span>;

+------------------------+-----------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+--------------------------------+---------+--------------+-------------------+-------------+-------+-------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------------+
| business_id            | full_address                                        | hours                                                                                                                                                                                                                                               | open |         categories             |   city  | review_count |        name       |  longitude  | state | stars |  latitude | attributes                                                                                                                                          |    <span class="hljs-built_in">type</span>  | neighborhoods |
+------------------------+--------------+------+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+--------------------------------+---------+--------------+-------------------+-------------+-------+-------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------------+
| vcNAWiLM4dR7D2nwwJ7nCA | <span class="hljs-number">4840</span> E Indian School Rd Ste <span class="hljs-number">101</span> Phoenix, AZ <span class="hljs-number">85018</span>   | {<span class="hljs-string">&quot;Tuesday&quot;</span>:{<span class="hljs-string">&quot;close&quot;</span>:<span class="hljs-string">&quot;17:00&quot;</span>,<span class="hljs-string">&quot;open&quot;</span>:<span class="hljs-string">&quot;08:00&quot;</span>},<span class="hljs-string">&quot;Friday&quot;</span>:{<span class="hljs-string">&quot;close&quot;</span>:<span class="hljs-string">&quot;17:00&quot;</span>,<span class="hljs-string">&quot;open&quot;</span>:<span class="hljs-string">&quot;08:00&quot;</span>},<span class="hljs-string">&quot;Monday&quot;</span>:{<span class="hljs-string">&quot;close&quot;</span>:<span class="hljs-string">&quot;17:00&quot;</span>,<span class="hljs-string">&quot;open&quot;</span>:<span class="hljs-string">&quot;08:00&quot;</span>},<span class="hljs-string">&quot;Wednesday&quot;</span>:{<span class="hljs-string">&quot;close&quot;</span>:<span class="hljs-string">&quot;17:00&quot;</span>,<span class="hljs-string">&quot;open&quot;</span>:<span class="hljs-string">&quot;08:00&quot;</span>},<span class="hljs-string">&quot;Thursday&quot;</span>:{<span class="hljs-string">&quot;close&quot;</span>:<span class="hljs-string">&quot;17:00&quot;</span>,<span class="hljs-string">&quot;open&quot;</span>:<span class="hljs-string">&quot;08:00&quot;</span>},<span class="hljs-string">&quot;Sunday&quot;</span>:{},<span class="hljs-string">&quot;Saturday&quot;</span>:{}} | <span class="hljs-literal">true</span> | [<span class="hljs-string">&quot;Doctors&quot;</span>,<span class="hljs-string">&quot;Health &amp; Medical&quot;</span>] | Phoenix |      <span class="hljs-number">7</span>       | Eric Goldberg, MD | -<span class="hljs-number">111.983758</span> |   AZ  |  <span class="hljs-number">3.5</span>  | <span class="hljs-number">33.499313</span> | {<span class="hljs-string">&quot;By Appointment Only&quot;</span>:<span class="hljs-literal">true</span>,<span class="hljs-string">&quot;Good Ambience&quot;</span>:{},<span class="hljs-string">&quot;Parking&quot;</span>:{},<span class="hljs-string">&quot;Music&quot;</span>:{},<span class="hljs-string">&quot;Hair Types Specialized In&quot;</span>:{},<span class="hljs-string">&quot;Payment Types&quot;</span>:{},<span class="hljs-string">&quot;Dietary Restrictions&quot;</span>:{}} | business |      []       |
+-------------+--------------+-------+------+------------+------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+--------------------------------+---------+--------------+-------------------+-------------+-------+-------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------------+
<span class="hljs-number">1</span> row selected (<span class="hljs-number">0.514</span> seconds)
</code></pre>
</li>
</ol>
<h2 id="&#x4F7F;&#x7528;&#x7A97;&#x53E3;&#x51FD;&#x6570;&#x505A;&#x7B80;&#x5355;&#x7684;&#x67E5;&#x8BE2;">&#x4F7F;&#x7528;&#x7A97;&#x53E3;&#x51FD;&#x6570;&#x505A;&#x7B80;&#x5355;&#x7684;&#x67E5;&#x8BE2;</h2>
<ol>
<li><p>&#x83B7;&#x53D6; Yelp &#x7684;&#x4E1A;&#x52A1;&#x57FA;&#x7840;&#x8BC4;&#x8BBA;&#x6570;&#x636E;&#x3002;</p>
<pre><code class="lang-bash">SELECT name, city, review_count, row_number()
OVER (PARTITION BY city ORDER BY review_count DESC) AS rownum
FROM `business.json` LIMIT <span class="hljs-number">15</span>;  

+----------------------------------------+------------+---------------+---------+
|                  name                  |    city    | review_count  | rownum  |
+----------------------------------------+------------+---------------+---------+
| Cupz N<span class="hljs-string">&apos; Crepes                         | Ahwatukee  | 124           | 1       |
| My Wine Cellar                         | Ahwatukee  | 98            | 2       |
| Kathy&apos;</span>s Alterations                    | Ahwatukee  | <span class="hljs-number">12</span>            | <span class="hljs-number">3</span>       |
| McDonald<span class="hljs-string">&apos;s                             | Ahwatukee  | 7             | 4       |
| U-Haul                                 | Ahwatukee  | 5             | 5       |
| Hi-Health                              | Ahwatukee  | 4             | 6       |
| Healthy and Clean Living Environments  | Ahwatukee  | 4             | 7       |
| Active Kids Pediatrics                 | Ahwatukee  | 4             | 8       |
| Roberto&apos;</span>s Authentic Mexican Food       | Anthem     | <span class="hljs-number">117</span>           | <span class="hljs-number">1</span>       |
| Q to U BBQ                             | Anthem     | <span class="hljs-number">74</span>            | <span class="hljs-number">2</span>       |
| Outlets At Anthem                      | Anthem     | <span class="hljs-number">64</span>            | <span class="hljs-number">3</span>       |
| Dara Thai                              | Anthem     | <span class="hljs-number">56</span>            | <span class="hljs-number">4</span>       |
| Cafe Provence                          | Anthem     | <span class="hljs-number">53</span>            | <span class="hljs-number">5</span>       |
| Shanghai Club                          | Anthem     | <span class="hljs-number">50</span>            | <span class="hljs-number">6</span>       |
| Two Brothers Kitchen                   | Anthem     | <span class="hljs-number">43</span>            | <span class="hljs-number">7</span>       |
+----------------------------------------+------------+---------------+---------+
<span class="hljs-number">15</span> rows selected (<span class="hljs-number">0.67</span> seconds)
</code></pre>
</li>
<li><p>&#x68C0;&#x67E5;&#x6BCF;&#x4E2A;&#x4E1A;&#x52A1;&#x7684;&#x6570;&#x91CF;&#x76F8;&#x6BD4;&#x5728;&#x57CE;&#x5E02;&#x7684;&#x6240;&#x6709;&#x4E1A;&#x52A1;&#x7684;&#x5E73;&#x5747;&#x6570;&#x91CF;&#x7684;&#x8BC4;&#x8BBA;&#x3002;</p>
<pre><code class="lang-bash">SELECT name, city,review_count,
Avg(review_count) OVER (PARTITION BY City) AS city_reviews_avg
FROM `business.json` LIMIT <span class="hljs-number">15</span>;

+----------------------------------------+------------+---------------+---------------------+
|                  name                  |    city    | review_count  |  city_reviews_avg   |
+----------------------------------------+------------+---------------+---------------------+
| Hi-Health                              | Ahwatukee  | <span class="hljs-number">4</span>             | <span class="hljs-number">32.25</span>               |
| My Wine Cellar                         | Ahwatukee  | <span class="hljs-number">98</span>            | <span class="hljs-number">32.25</span>               |
| U-Haul                                 | Ahwatukee  | <span class="hljs-number">5</span>             | <span class="hljs-number">32.25</span>               |
| Cupz N<span class="hljs-string">&apos; Crepes                         | Ahwatukee  | 124           | 32.25               |
| McDonald&apos;</span>s                             | Ahwatukee  | <span class="hljs-number">7</span>             | <span class="hljs-number">32.25</span>               |
| Kathy<span class="hljs-string">&apos;s Alterations                    | Ahwatukee  | 12            | 32.25               |
| Healthy and Clean Living Environments  | Ahwatukee  | 4             | 32.25               |
| Active Kids Pediatrics                 | Ahwatukee  | 4             | 32.25               |
| Anthem Community Center                | Anthem     | 4             | 14.492063492063492  |
| Scrapbooks To Remember                 | Anthem     | 4             | 14.492063492063492  |
| Hungry Howie&apos;</span>s Pizza                   | Anthem     | <span class="hljs-number">7</span>             | <span class="hljs-number">14.492063492063492</span>  |
| Pinata Nueva                           | Anthem     | <span class="hljs-number">3</span>             | <span class="hljs-number">14.492063492063492</span>  |
| Starbucks Coffee Company               | Anthem     | <span class="hljs-number">13</span>            | <span class="hljs-number">14.492063492063492</span>  |
| Pizza Hut                              | Anthem     | <span class="hljs-number">6</span>             | <span class="hljs-number">14.492063492063492</span>  |
| Rays Pizza                             | Anthem     | <span class="hljs-number">19</span>            | <span class="hljs-number">14.492063492063492</span>  |
+----------------------------------------+------------+---------------+---------------------+
<span class="hljs-number">15</span> rows selected (<span class="hljs-number">0.395</span> seconds)
</code></pre>
</li>
<li><p>&#x68C0;&#x67E5;&#x6BCF;&#x4E2A;&#x4F01;&#x4E1A;&#x7684;&#x8BC4;&#x8BBA;&#x6570;&#x91CF;&#x4E3A;&#x57CE;&#x5E02;&#x7684;&#x6240;&#x6709;&#x4E1A;&#x52A1;&#x7684;&#x603B;&#x6570;&#x91CF;&#x7684;&#x8D21;&#x732E;&#x3002;</p>
<pre><code class="lang-bash">SELECT name, city,review_count,
Sum(review_count) OVER (PARTITION BY City) AS city_reviews_sum
FROM `business.json`<span class="hljs-built_in">limit</span> <span class="hljs-number">15</span>;

+----------------------------------------+------------+---------------+-------------------+
|                  name                  |    city    | review_count  | city_reviews_sum  |
+----------------------------------------+------------+---------------+-------------------+
| Hi-Health                              | Ahwatukee  | <span class="hljs-number">4</span>             | <span class="hljs-number">258</span>               |
| My Wine Cellar                         | Ahwatukee  | <span class="hljs-number">98</span>            | <span class="hljs-number">258</span>               |
| U-Haul                                 | Ahwatukee  | <span class="hljs-number">5</span>             | <span class="hljs-number">258</span>               |
| Cupz N<span class="hljs-string">&apos; Crepes                         | Ahwatukee  | 124           | 258               |
| McDonald&apos;</span>s                             | Ahwatukee  | <span class="hljs-number">7</span>             | <span class="hljs-number">258</span>               |
| Kathy<span class="hljs-string">&apos;s Alterations                    | Ahwatukee  | 12            | 258               |
| Healthy and Clean Living Environments  | Ahwatukee  | 4             | 258               |
| Active Kids Pediatrics                 | Ahwatukee  | 4             | 258               |
| Anthem Community Center                | Anthem     | 4             | 913               |
| Scrapbooks To Remember                 | Anthem     | 4             | 913               |
| Hungry Howie&apos;</span>s Pizza                   | Anthem     | <span class="hljs-number">7</span>             | <span class="hljs-number">913</span>               |
| Pinata Nueva                           | Anthem     | <span class="hljs-number">3</span>             | <span class="hljs-number">913</span>               |
| Starbucks Coffee Company               | Anthem     | <span class="hljs-number">13</span>            | <span class="hljs-number">913</span>               |
| Pizza Hut                              | Anthem     | <span class="hljs-number">6</span>             | <span class="hljs-number">913</span>               |
| Rays Pizza                             | Anthem     | <span class="hljs-number">19</span>            | <span class="hljs-number">913</span>               |
+----------------------------------------+------------+---------------+-------------------+
<span class="hljs-number">15</span> rows selected (<span class="hljs-number">0.543</span> seconds)
</code></pre>
</li>
</ol>
<h2 id="&#x4F7F;&#x7528;&#x7A97;&#x53E3;&#x51FD;&#x6570;&#x8FDB;&#x884C;&#x590D;&#x6742;&#x67E5;&#x8BE2;">&#x4F7F;&#x7528;&#x7A97;&#x53E3;&#x51FD;&#x6570;&#x8FDB;&#x884C;&#x590D;&#x6742;&#x67E5;&#x8BE2;</h2>
<ol>
<li><p>&#x6392;&#x540D;&#x524D; 10 &#x540D;&#x7684;&#x57CE;&#x5E02;&#x548C;&#x4ED6;&#x4EEC;&#x7684;&#x6392;&#x540D;&#x6700;&#x9AD8;&#x7684;&#x4F01;&#x4E1A;&#x6570;&#x91CF;&#x7684;&#x8BC4;&#x8BBA;&#x3002;&#x4F7F;&#x7528; Drill &#x7A97;&#x53E3;&#x51FD;&#x6570;&#xFF0C;&#x4F8B;&#x5982; rank&#xFF0C;dense_sank&#x3002;</p>
<pre><code>WITH X
AS
(SELECT name, city, review_count,
RANK()
OVER (PARTITION BY city
ORDER BY review_count DESC) AS review_rank
FROM `business.json`)
SELECT X.name, X.city, X.review_count
FROM X
WHERE X.review_rank =1 ORDER BY review_count DESC LIMIT 10;

+-------------------------------------------+-------------+---------------+
|                   name                    |    city     | review_count  |
+-------------------------------------------+-------------+---------------+
| Mon Ami Gabi                              | Las Vegas   | 4084          |
| Studio B                                  | Henderson   | 1336          |
| Phoenix Sky Harbor International Airport  | Phoenix     | 1325          |
| Four Peaks Brewing Co                     | Tempe       | 1110          |
| The Mission                               | Scottsdale  | 783           |
| Joe&apos;s Farm Grill                          | Gilbert     | 770           |
| The Old Fashioned                         | Madison     | 619           |
| Cornish Pasty Company                     | Mesa        | 578           |
| SanTan Brewing Company                    | Chandler    | 469           |
| Yard House                                | Glendale    | 321           |
+-------------------------------------------+-------------+---------------+
10 rows selected (0.49 seconds)
</code></pre></li>
<li><p>&#x5728;&#x57CE;&#x5E02;&#x7684;&#x9876;&#x90E8;&#x548C;&#x5E95;&#x90E8;&#x7684;&#x8BC4;&#x8BBA;&#x8BA1;&#x6570;&#x7684;&#x6BCF;&#x4E2A;&#x4E1A;&#x52A1;&#x7684;&#x8BC4;&#x8BBA;&#x6570;&#x91CF;&#x6BD4;&#x8F83;&#x3002;</p>
<pre><code>SELECT name, city, review_count,
FIRST_VALUE(review_count)
OVER(PARTITION BY city ORDER BY review_count DESC) AS top_review_count,
LAST_VALUE(review_count)
OVER(PARTITION BY city ORDER BY review_count DESC) AS bottom_review_count
FROM `business.json` limit 15;

+----------------------------------------+------------+---------------+-------------------+----------------------+
|                  name                  |    city    | review_count  | top_review_count  | bottom_review_count  |
+----------------------------------------+------------+---------------+-------------------+----------------------+
| My Wine Cellar                         | Ahwatukee  | 98            | 124               | 12                   |
| McDonald&apos;s                             | Ahwatukee  | 7             | 124               | 12                   |
| U-Haul                                 | Ahwatukee  | 5             | 124               | 12                   |
| Hi-Health                              | Ahwatukee  | 4             | 124               | 12                   |
| Healthy and Clean Living Environments  | Ahwatukee  | 4             | 124               | 12                   |
| Active Kids Pediatrics                 | Ahwatukee  | 4             | 124               | 12                   |
| Cupz N&apos; Crepes                         | Ahwatukee  | 124           | 124               | 12                   |
| Kathy&apos;s Alterations                    | Ahwatukee  | 12            | 124               | 12                   |
| Q to U BBQ                             | Anthem     | 74            | 117               | 117                  |
| Dara Thai                              | Anthem     | 56            | 117               | 117                  |
| Cafe Provence                          | Anthem     | 53            | 117               | 117                  |
| Shanghai Club                          | Anthem     | 50            | 117               | 117                  |
| Two Brothers Kitchen                   | Anthem     | 43            | 117               | 117                  |
| The Tennessee Grill                    | Anthem     | 32            | 117               | 117                  |
| Dollyrockers Boutique and Salon        | Anthem     | 30            | 117               | 117                  |
+----------------------------------------+------------+---------------+-------------------+----------------------+
15 rows selected (0.516 seconds)
</code></pre></li>
<li><p>&#x6BD4;&#x8F83;&#x524D;&#x540E;&#x7684;&#x4E1A;&#x52A1;&#x8BC4;&#x8BBA;&#x6570;&#x91CF;&#x3002;</p>
<pre><code>SELECT city, review_count, name,
LAG(review_count, 1) OVER(PARTITION BY city ORDER BY review_count DESC)
AS preceding_count,
LEAD(review_count, 1) OVER(PARTITION BY city ORDER BY review_count DESC)
AS following_count
FROM `business.json` limit 15;

+------------+---------------+----------------------------------------+------------------+------------------+
|    city    | review_count  |                  name                  | preceding_count  | following_count  |
+------------+---------------+----------------------------------------+------------------+------------------+
| Ahwatukee  | 124           | Cupz N&apos; Crepes                         | null             | 98               |
| Ahwatukee  | 98            | My Wine Cellar                         | 124              | 12               |
| Ahwatukee  | 12            | Kathy&apos;s Alterations                    | 98               | 7                |
| Ahwatukee  | 7             | McDonald&apos;s                             | 12               | 5                |
| Ahwatukee  | 5             | U-Haul                                 | 7                | 4                |
| Ahwatukee  | 4             | Hi-Health                              | 5                | 4                |
| Ahwatukee  | 4             | Healthy and Clean Living Environments  | 4                | 4                |
| Ahwatukee  | 4             | Active Kids Pediatrics                 | 4                | null             |
| Anthem     | 117           | Roberto&apos;s Authentic Mexican Food       | null             | 74               |
| Anthem     | 74            | Q to U BBQ                             | 117              | 64               |
| Anthem     | 64            | Outlets At Anthem                      | 74               | 56               |
| Anthem     | 56            | Dara Thai                              | 64               | 53               |
| Anthem     | 53            | Cafe Provence                          | 56               | 50               |
| Anthem     | 50            | Shanghai Club                          | 53               | 43               |
| Anthem     | 43            | Two Brothers Kitchen                   | 50               | 32               |
+------------+---------------+----------------------------------------+------------------+------------------+
15 rows selected (0.518 seconds)
</code></pre></li>
</ol>
<footer class="page-footer"><span class="copyright">Copyright &#xA9; http://drill.smartloli.org 2016 all right reserved&#xFF0C;powered by Gitbook</span><span class="footer-modification">Modify: 
2016-01-12 10:27:48
</span></footer>
                    
                    </section>
                
                
                </div>
            </div>
        </div>

        
        <a href="../3.教程/6.社会媒体分析.html" class="navigation navigation-prev " aria-label="Previous page: 6.社会媒体分析"><i class="fa fa-angle-left"></i></a>
        
        
        <a href="../4.安装 Drill/1.Drill 安装介绍.html" class="navigation navigation-next " aria-label="Next page: 1.Drill 安装介绍"><i class="fa fa-angle-right"></i></a>
        
    </div>
</div>

        
<script src="../gitbook/app.js"></script>

    
    <script src="../gitbook/plugins/gitbook-plugin-baidu/plugin.js"></script>
    

    
    <script src="../gitbook/plugins/gitbook-plugin-sharing/buttons.js"></script>
    

    
    <script src="../gitbook/plugins/gitbook-plugin-fontsettings/buttons.js"></script>
    

    
    <script src="../gitbook/plugins/gitbook-plugin-livereload/plugin.js"></script>
    

<script>
require(["gitbook"], function(gitbook) {
    var config = {"tbfed-pagefooter":{"copyright":"Copyright &copy http://drill.smartloli.org 2016","modify_label":"Modify: ","modify_format":"YYYY-MM-DD HH:mm:ss"},"baidu":{"token":""},"fontsettings":{"size":1,"theme":"white","family":"sans"},"highlight":{},"sharing":{"facebook":true,"twitter":true,"google":false,"weibo":false,"instapaper":false,"vk":false,"all":["facebook","google","twitter","weibo","instapaper"]},"livereload":{}};
    gitbook.start(config);
});
</script>

        <script>var _hmt = _hmt || [];(function() {var hm = document.createElement('script');hm.src = '//hm.baidu.com/hm.js?';var s = document.getElementsByTagName('script')[0];s.parentNode.insertBefore(hm, s);})();</script>
    </body>
    
</html>
